package com.ce.tag;

import java.io.FileInputStream;
import java.io.FileOutputStream;

import org.apache.poi2.hssf.usermodel.HSSFCell;
import org.apache.poi2.hssf.usermodel.HSSFRow;
import org.apache.poi2.hssf.usermodel.HSSFSheet;
import org.apache.poi2.hssf.usermodel.HSSFWorkbook;
import org.apache.poi2.hssf.util.Region;

public class ExcelUtil {
	public static void main(String[] arg) throws Exception{
		HSSFWorkbook wb = null;
		wb = new HSSFWorkbook(new FileInputStream("C:/2.xls"));
		HSSFSheet sheet = wb.getSheetAt(0);
		mergedRows(sheet,0);
		mergedRows(sheet,1);
		wb.write(new FileOutputStream("C:/1.xls"));
	}

	/** 判断指定位置是否有合并单元格 */
	public static Region getMergedRegion(HSSFSheet sheet, int row, int column) {
		int sheetMergeCount = sheet.getNumMergedRegions();
		for (int i = 0; i < sheetMergeCount; i++) {
			Region ca = sheet.getMergedRegionAt(i);
			int firstColumn = ca.getColumnFrom();
			int lastColumn = ca.getColumnTo();
			int firstRow = ca.getRowFrom();
			int lastRow = ca.getRowTo();
			if (row >= firstRow && row <= lastRow) {
				if (column >= firstColumn && column <= lastColumn) {
					return ca;
				}
			}
		}
		return null;
	}

	/**
	 * 在Excel文件中的指定列进行遍历，如找到相同内容的单元格，则合并之
	 * @param sheet
	 * @param col
	 */
	public static void mergedRows(HSSFSheet sheet,int col){
		mergedRows(sheet, col, 0, sheet.getLastRowNum(),false,0);
	}
	/**
	 * 在Excel文件中的指定列进行遍历，如找到相同内容的单元格，则合并之
	 * @param sheet
	 * @param col
	 * @param beginRow
	 * @param endRow
	 */
	public static void mergedRows(HSSFSheet sheet,int col,int beginRow,int endRow,boolean isAfterIndex,int relyPreCell){
		if(sheet==null){
			return;
		}
		short scol = (short) col;
		String text = null;
		int begin = 0;
		beginRow = Math.max(beginRow, sheet.getFirstRowNum());
		endRow = Math.min(endRow, sheet.getLastRowNum());
		for(int i=beginRow;i<=endRow;i++){
			HSSFRow row = sheet.getRow(i);
			HSSFCell cell = row==null?null:row.getCell(scol);
			String tmp = cell==null?null:(cell.getCellType()==HSSFCell.CELL_TYPE_STRING?cell.getStringCellValue():""+cell.getNumericCellValue());
			String cellText = getText(sheet,i,scol,relyPreCell);
			boolean merged = getMergedRegion(sheet,i,scol)!=null;
			if(!merged&&text==null&&tmp!=null){
				text = cellText;
				begin = i;
			}
			if(text!=null&&(i==endRow||merged||(!text.equals(cellText)))){
				if(i>begin+1){
					sheet.addMergedRegion(new Region(begin,scol,i-1,scol));
				}
				text = getText(sheet,i,scol,relyPreCell);
				begin = i;
			}
		}
	}
	private static String getText(HSSFSheet sheet,int row,int col,int relyPreCell){
		HSSFRow _row = sheet.getRow(row);
		HSSFCell cell = _row==null?null:_row.getCell((short) col);
		String text = cell==null?"":(cell.getCellType()==HSSFCell.CELL_TYPE_STRING?cell.getStringCellValue():""+cell.getNumericCellValue());
		relyPreCell = Math.min(col, relyPreCell);
		int i=0;
		while(i++<col&&relyPreCell-->0){
			cell = _row==null?null:_row.getCell((short) (col-i));
			String tmp = cell==null?"":(cell.getCellType()==HSSFCell.CELL_TYPE_STRING?cell.getStringCellValue():""+cell.getNumericCellValue());
			text = tmp.replace('_','-')+"_"+text;
		}
		return text;
	}

}
